# app/core/sql_database.py

import json
import os
from datetime import datetime, timezone, timedelta
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Text, DateTime, Integer, JSON
from sqlalchemy.sql import func
from app.core.snowflake import generate_id

def get_china_time():
    """获取中国大陆时间（UTC+8）"""
    utc_now = datetime.now(timezone.utc)
    china_tz = timezone(timedelta(hours=8))
    return utc_now.astimezone(china_tz)

# 使用服务器上的MySQL数据库
SQLALCHEMY_DATABASE_URI = (
    "mysql+pymysql://root:iScene1234@10.3.244.23:13308/iScene_test"
    "?charset=utf8mb4&autocommit=true&use_unicode=1&connect_timeout=10"
)

# 数据库文件路径（用于日志显示）
DB_PATH = "MySQL数据库: 10.3.244.23:13308/iScene_test"

# 创建引擎
engine = create_engine(
    SQLALCHEMY_DATABASE_URI,
    echo=False,  # 设置为True可以打印SQL语句用于调试
    json_serializer=lambda obj: json.dumps(obj, ensure_ascii=False),
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base: DeclarativeMeta = declarative_base()

class MessageModel(Base):
    """消息模型"""
    __tablename__ = "message"
    
    id = Column(String(32), primary_key=True)
    conversation_id = Column(String(32), nullable=False, index=True)
    chat_type = Column(String(50), nullable=False)
    query = Column(Text, nullable=False)
    response = Column(Text, nullable=False, default="")
    meta_data = Column(JSON, nullable=False, default={})
    feedback_score = Column(Integer, nullable=False, default=-1)
    feedback_reason = Column(String(255), nullable=True)
    create_time = Column(DateTime, nullable=False, default=get_china_time)

def init_database():
    """初始化数据库"""
    Base.metadata.create_all(bind=engine)
    print(f"数据库初始化完成: {DB_PATH}")

def get_db_session():
    """获取数据库会话"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

def add_message_to_db(
    conversation_id: str,
    chat_type: str,
    query: str,
    response: str = "",
    message_id: str = None,
    meta_data: dict = {},
    feedback_reason: str = None,
):
    """添加消息到数据库"""
    import logging
    
    logger = logging.getLogger(__name__)
    logger.info(f"开始保存消息到数据库: conversation_id={conversation_id}, chat_type={chat_type}")
    logger.info(f"查询内容长度: {len(query)}, 回答内容长度: {len(response)}")
    
    if not message_id:
        message_id = generate_id()
    
    logger.info(f"生成的消息ID: {message_id}")
    logger.info(f"数据库文件路径: {DB_PATH}")
    
    db = SessionLocal()
    try:
        logger.info("创建MessageModel对象...")
        message = MessageModel(
            id=message_id,
            conversation_id=conversation_id,
            chat_type=chat_type,
            query=query,
            response=response,
            meta_data=meta_data,
            feedback_reason=feedback_reason,
        )
        
        logger.info("添加到数据库会话...")
        db.add(message)
        
        logger.info("提交事务...")
        db.commit()
        
        logger.info(f"消息保存成功，ID: {message.id}")
        
        # 验证数据是否真的保存了
        logger.info("验证数据保存...")
        saved_message = db.query(MessageModel).filter_by(id=message_id).first()
        if saved_message:
            logger.info(f"验证成功: 找到保存的消息，ID={saved_message.id}, conversation_id={saved_message.conversation_id}")
        else:
            logger.error("验证失败: 无法找到刚保存的消息")
        
        return message.id
    except Exception as e:
        logger.error(f"保存消息到数据库失败: {e}")
        logger.error(f"错误类型: {type(e).__name__}")
        import traceback
        logger.error(f"详细错误信息: {traceback.format_exc()}")
        db.rollback()
        raise e
    finally:
        logger.info("关闭数据库会话")
        db.close()

def get_message_by_id(message_id: str):
    """根据ID获取消息"""
    db = SessionLocal()
    try:
        return db.query(MessageModel).filter_by(id=message_id).first()
    finally:
        db.close()

def filter_message(conversation_id: str, limit: int = 10):
    """获取会话历史消息"""
    db = SessionLocal()
    try:
        messages = (
            db.query(MessageModel)
            .filter_by(conversation_id=conversation_id)
            .filter(MessageModel.response != "")
            .order_by(MessageModel.create_time.desc())
            .limit(limit)
            .all()
        )
        data = []
        for m in messages:
            data.append({"query": m.query, "response": m.response})
        return data
    finally:
        db.close()

def get_conversation_history(conversation_id: str, limit: int = 5):
    """获取对话历史，用于多轮对话"""
    db = SessionLocal()
    try:
        messages = (
            db.query(MessageModel)
            .filter_by(conversation_id=conversation_id)
            .filter(MessageModel.response != "")
            .order_by(MessageModel.create_time.asc())  # 按时间正序，保持对话顺序
            .limit(limit)
            .all()
        )
        
        # 构建对话历史
        history = []
        for msg in messages:
            history.append({
                "role": "user",
                "content": msg.query
            })
            history.append({
                "role": "assistant", 
                "content": msg.response
            })
        
        return history
    finally:
        db.close()

def get_all_messages(limit: int = 50):
    """获取所有消息记录（用于调试）"""
    import logging
    logger = logging.getLogger(__name__)
    
    db = SessionLocal()
    try:
        logger.info("查询所有消息记录...")
        messages = db.query(MessageModel).order_by(MessageModel.create_time.desc()).limit(limit).all()
        logger.info(f"找到 {len(messages)} 条消息记录")
        
        for i, msg in enumerate(messages):
            logger.info(f"消息 {i+1}: ID={msg.id}, conversation_id={msg.conversation_id}, "
                       f"chat_type={msg.chat_type}, create_time={msg.create_time}")
        
        return messages
    finally:
        db.close()

def check_database_status():
    """检查数据库状态"""
    import logging
    
    logger = logging.getLogger(__name__)
    
    logger.info(f"数据库连接: {DB_PATH}")
    logger.info(f"数据库URI: {SQLALCHEMY_DATABASE_URI}")
    
    # 检查表结构
    db = SessionLocal()
    try:
        from sqlalchemy import inspect
        inspector = inspect(engine)
        tables = inspector.get_table_names()
        logger.info(f"数据库中的表: {tables}")
        
        if 'message' in tables:
            columns = inspector.get_columns('message')
            logger.info(f"message表的列: {[col['name'] for col in columns]}")
            
            # 检查记录数
            count = db.query(MessageModel).count()
            logger.info(f"message表中的记录数: {count}")
        else:
            logger.error("message表不存在")
    except Exception as e:
        logger.error(f"检查数据库状态失败: {e}")
        import traceback
        logger.error(f"详细错误信息: {traceback.format_exc()}")
    finally:
        db.close() 